import pandas as pd
import sqlite3

# 连接到数据库（如果不存在，则会创建）
conn = sqlite3.connect('./data/pl5.db')
# 创建游标对象，用于执行SQL语句
cursor = conn.cursor()
# 查询所有数据
select_sql = "select * from pl5"
cursor.execute(select_sql)
pl5_data = cursor.fetchall()
# 构建Dataframe
df = pd.DataFrame(pl5_data)
df.columns = ['lotteryDrawNum', "lotteryDrawTime", 'n1', 'n2', 'n3', 'n4', 'n5']

res = []
# 统计数据
for i in range(1, 6):
    num_count = df['n{}'.format(i)].value_counts(ascending=False)
    res.append(num_count.to_dict())
# print(res)

# 构建可视化
from pyecharts.charts import Pie, Bar
from pyecharts.charts import Grid, Page
from pyecharts import options as opts
from pyecharts.globals import ThemeType

num_list = list(range(0, 10))

# 创建图表对象
grid = Page()
for i, r in enumerate(res):
    # 创建图表对象
    bar = Bar()
    # 添加x轴坐标
    bar.add_xaxis(num_list)

    # 添加y轴坐标
    t = sorted(r.items(), key=lambda x: x[0])
    d = dict(t)
    bar.add_yaxis(f'n{i + 1}', list(d.values()))
    # x 轴标题
    bar.set_global_opts(
        title_opts=opts.TitleOpts(
            # 添加图表标题
            title=f'第{i + 1}个位置数字统计'
        ),
        xaxis_opts={'name': '出现的数字'},
        yaxis_opts={'name': '出现数字的次数'}
    )

    grid.add(bar)
# render 渲染
grid.render()
